package com.authine.cloudpivot.ext.utils;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.lang.Console;
import com.alibaba.cola.dto.RequestContext;
import com.alibaba.cola.exception.BizException;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.authine.cloudpivot.ext.dto.Page;
import com.authine.cloudpivot.ext.utils.convert.BOConvert;
import com.authine.hermes.app.launcher.service.BOService;
import com.authine.mvp.app.launcher.common.TableHelper;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import com.authine.mvp.app.launcher.dto.bo.query.ListBOQuery;
import com.authine.mvp.app.launcher.dto.bo.query.expression.*;
import com.authine.mvp.app.launcher.dto.bo.response.BOList;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.lang.Nullable;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class JdbcTemplateUtils {
    private static BOService boService = ApplicationContextUtils.getBean(BOService.class);
    private static NamedParameterJdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(NamedParameterJdbcTemplate.class);
    private LinkedList<ComplexCondition> condition;
    private String schema;
    private String appCode;
    private StringBuilder select;
    private Map<String, Object> fieldAndVal;
    private Map<String, Object>[] fieldAndValList;
    private Map<String, Object> param;
    private List<String> orderBy;
    private Page page;


    public JdbcTemplateUtils() {

    }

    private JdbcTemplateUtils(String appCode, String schema) {
        this.schema = schema;
        this.appCode = appCode.toLowerCase();
    }

    public JdbcTemplateUtils(String schema) {
        this.appCode = null;
        this.schema = schema;
    }


    public static JdbcTemplateUtils builder(String tbName){

        return  new JdbcTemplateUtils(tbName);
    }
    public static JdbcTemplateUtils builder(String appCode,String tbName){

        return  new JdbcTemplateUtils(appCode,tbName);
    }

    public static String getTableName(String schemaCode){
        return ApplicationContextUtils.getBean(TableHelper.class).getTableName(schemaCode);
//        return schemaCode;
    }

    public static String getTableName(String schemaCode, String appCode){
        if (StringUtils.isNotEmpty(appCode)) {
            return String.format("%s.i_%s_%s", appCode , appCode ,schemaCode.toLowerCase());
        }{
            return getTableName(schemaCode);
        }

    }

    private JdbcTemplateUtils and(){
        ComplexCondition complexCondition = new ComplexCondition();
        complexCondition.setNature(Nature.AND);
        condition = new LinkedList<>();
        condition.add(complexCondition );
        return  this;
    }

    public JdbcTemplateUtils or(){
        ComplexCondition complexCondition = new ComplexCondition();
        complexCondition.setNature(Nature.OR);
        if (condition == null) {
            condition = new LinkedList<>();
        }
        condition.add(complexCondition );
        return  this;
    }

    private void addSimpleCondition(SimpleCondition simpleCondition){
        if(ObjectUtils.isEmpty(simpleCondition.getValue())){
            return;
        }
        if (condition == null) {
            this.and();
        }
        List<Condition> conditions = condition.getFirst().getConditions();
        if (conditions == null) {
            conditions = new ArrayList<>();
        }
        conditions.add(simpleCondition);
    }

    public JdbcTemplateUtils eq(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.EQ,value);

        addSimpleCondition(simpleCondition);
        return  this;
    }

    public JdbcTemplateUtils gt(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.GT,value);

        addSimpleCondition(simpleCondition);
        return  this;
    }

    public JdbcTemplateUtils lt(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.LT,value);

        addSimpleCondition(simpleCondition);
        return  this;
    }

    public JdbcTemplateUtils gte(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.GTE,value);

        addSimpleCondition(simpleCondition);
        return  this;
    }

    public JdbcTemplateUtils lte(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.LTE,value);

        addSimpleCondition(simpleCondition);
        return  this;
    }

    public JdbcTemplateUtils notEq(String field, Object value){
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.NOT_EQ,value);
        addSimpleCondition(simpleCondition);
        return  this;
    }


    public JdbcTemplateUtils in(String field, Object value){
        Object val = value;
        if (ObjectUtils.isEmpty(value)) {
            return this;
        } else if (!(value instanceof Collection)) {
            val = Collections.singleton(value);
        }
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.IN, val);
        addSimpleCondition(simpleCondition);
        return this;
    }

    public JdbcTemplateUtils notIn(String field, Object value) {
        Object val = value;
        if (ObjectUtils.isEmpty(value)) {
            return this;
        } else if (!(value instanceof Collection)) {
            val = Collections.singleton(value);
        }
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.NOT_IN, value);
        addSimpleCondition(simpleCondition);
        return this;
    }

    public JdbcTemplateUtils like(String field, Object value) {
        SimpleCondition simpleCondition = new SimpleCondition(field, Operator.LIKE, value);

        addSimpleCondition(simpleCondition);
        return this;
    }

    /**
     * 通过自定义SQL，返回指定自定义云枢BO对象的集合
     *
     * @param sql          自定义查询的sql
     * @param param        查询条件
     * @param requiredType
     * @return requiredType 对象的集合
     */
    public static <T> List<T> queryForPOJOList(String sql, Map<String, Object> param, String schema, Class<T> requiredType) {
        return queryForPOJOList(sql, param, schema, requiredType, false);
    }

    /**
     * 通过自定义SQL，返回指定自定义云枢BO对象的集合
     *
     * @param sql           自定义查询的sql
     * @param param         查询条件
     * @param schema        领域模型编码
     * @param requiredType  对象的集合
     * @param isRadioToList 选人控件是否转成集合
     * @return 列表集合
     */
    public static <T> List<T> queryForPOJOList(String sql, Map<String, Object> param, String schema, Class<T> requiredType, boolean isRadioToList) {
        List<Map<String, Object>> resultMap = queryForList(sql, param);
        BoResultFormat.resultFormat(resultMap, schema, isRadioToList);
        return JSON.parseArray(JSON.toJSONString(resultMap), requiredType);
    }


    public JdbcTemplateUtils desc(String field) {
        if (orderBy == null) {
            orderBy = new ArrayList<>();
        }

        orderBy.add(field.concat(" desc"));

        return this;
    }

    public JdbcTemplateUtils asc(String field) {
        if (orderBy == null) {
            orderBy = new ArrayList<>();
        }
        orderBy.add(field.concat(" asc"));
        return this;
    }

    public JdbcTemplateUtils sort(String sortStr) {
        if (orderBy == null) {
            orderBy = new ArrayList<>();
        }
        orderBy.add(sortStr);
        return this;
    }

    public JdbcTemplateUtils page(Integer pageIndex, Integer pageSize) {
        this.page = new Page();
        this.page.setPageIndex(pageIndex);
        this.page.setPageSize(pageSize);
        return this;
    }

    @Nullable
    public JdbcTemplateUtils selectField(String slt) {

        if (select == null) {
            this.select = new StringBuilder(slt);
        } else {
            this.select.append(",").append(slt);
        }
        return this;
    }

    @Nullable
    public JdbcTemplateUtils newField(String slt) {
        this.select = new StringBuilder(slt);
        return this;
    }

    /**
     * 返回javaBean , 下划转驼峰,

     *
     * @param <T>
     * @return
     */
    public <T> List<T> queryForJavaBean(Class<T> clazz) {
        BeanPropertyRowMapper<T> tBeanPropertyRowMapper = BeanPropertyRowMapper.newInstance(clazz, new BOConvert());
        List<T> result = jdbcTemplate.query(querySql(), param, tBeanPropertyRowMapper);
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }



    /**
     * 返回集成类型的
     *
     * @param requiredType
     * @param <T>
     * @return
     */
    public <T> T queryForObject(Class<T> requiredType) {
        try {
            return jdbcTemplate.queryForObject(querySql(), param,requiredType);
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 返回云枢BO复杂对象 不能夸应用
     *
     * @param requiredType
     * @param <T>
     * @return
     */
    public <T> T queryForPOJO(Class<T> requiredType) {
       return queryForPOJO(requiredType, false);
    }

    /**
     * 返回云枢BO复杂对象 不能夸应用
     *
     * @param requiredType
     * @param isRadioToList 是否将人员和部门单选字段转成list
     * @param <T>
     * @return
     */
    public <T> T queryForPOJO(Class<T> requiredType, boolean isRadioToList) {
        List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql(), param);
        if(CollectionUtils.isEmpty(result)){
            return null;
        }
        if (result.size()>1){
            throw  new BizException("数据集存在多个对象");
        }
        BoResultFormat.resultFormat(result,this.schema, isRadioToList);
        return JSONObject.parseObject(JSONObject.toJSONString(result.get(0)), requiredType);
    }

    public Map<String, Object> queryForMap() {
        try {
            return jdbcTemplate.queryForMap(querySql(), param);
        } catch (Exception e) {
            return null;
        }
    }


    public <T> List<T> queryForList(Class<T> requiredType) {

        return jdbcTemplate.queryForList(querySql(), param, requiredType);
    }

    public <T> List<T> queryForPOJOList(Class<T> requiredType) {
       return queryForPOJOList(requiredType, false);
    }

    /**
     * 返回云枢BO复杂对象 不能夸应用
     *
     * @param requiredType
     * @param isRadioToList 是否将人员和部门单选字段转成list
     * @param <T>
     * @return
     */
    public <T> List<T> queryForPOJOList(Class<T> requiredType, boolean isRadioToList) {
        List<Map<String, Object>> resultMap = queryForList(querySql(), param);
        BoResultFormat.resultFormat(resultMap, this.schema, isRadioToList);
        return JSON.parseArray(JSON.toJSONString(resultMap), requiredType);
    }

    public static List<Map<String,Object>> queryForList(String sql, Map<String,Object> param){
        return   jdbcTemplate.queryForList(sql, param);
    }

    public  List<Map<String,Object>> queryForList(){

        return jdbcTemplate.queryForList(querySql(), param);
    }

    private String querySql(){

        param = new HashMap<>();
        Assert.notNull(select, "查询字段不能为空");
        Assert.notNull(schema, "表命不能为空");

        //补充默认查询 deleted = 0 的数据 ; 系统删除是逻辑删除
        eq("deleted", 0);

        StringBuilder sl = new StringBuilder("select ").append(select);

        sl.append(" from ").append(getTableName(schema, appCode));

        sl.append(where());

        if (orderBy != null && orderBy.size() > 0) {
            sl.append(" order by ").append(StringUtils.join(orderBy, ","));
        }

        if (page != null) {
            sl.append(page.limit());
        }

        Console.log("================select sql={}", sl.toString());
        Console.log("================select param={}", param);

        return sl.toString();

    }


    public JdbcTemplateUtils putFieldAndVal(String field, Object val){
        if (this.fieldAndVal == null) {
            this.fieldAndVal = new HashMap<>();
        }
        this.fieldAndVal.put(field, val);
        return  this;
    }

    public JdbcTemplateUtils putFieldAndVal(Map<String,Object> fieldAndVal){
        if (this.fieldAndVal == null) {
            this.fieldAndVal = new HashMap<>();
        }
        this.fieldAndVal.putAll(fieldAndVal);
        return  this;
    }

    public JdbcTemplateUtils putFieldAndValList(Map<String,Object>[] fieldAndValList){

        if (this.fieldAndValList == null){
            this.fieldAndValList = new Map[fieldAndValList.length];

            for (int i = 0; i < fieldAndValList.length; i++) {
                this.fieldAndValList[i] = fieldAndValList[i];
            }
        }else{

            Map<String,Object>[] mapA =  new Map[fieldAndValList.length + this.fieldAndValList.length];

            int indexArr = 0; // 手动指定arr的下标
            int indexBrr = 0; // 手动指定brr的下标
            for (int i = 0; i < mapA.length; i++) {
                if (i < this.fieldAndValList.length) {

                    mapA[i] = this.fieldAndValList[indexArr];
                    indexArr++;

                } else {

                    if (i > fieldAndValList.length) {
                        mapA[i] = fieldAndValList[indexBrr];
                        indexBrr++;
                    }
                }
            }
            this.fieldAndValList = fieldAndValList;

        }

        return this;
    }

    /**
     * 删除是逻辑删除,不是物理删除, 修改 deleted , 1 是删除, 0 是为删除
     *
     * @return
     */
    public int deleted() {
        this.putFieldAndVal("deleted", 1);
        return this.update();
    }

    public int update() {
        //
        param = new HashMap<>();

        Assert.notEmpty(fieldAndVal, "set字段和值不能为空");
        Assert.notNull(schema, "表命不能为空");
        Assert.notEmpty(condition, "update where 条件不能为空");

        StringBuilder sl = new StringBuilder("update ").append(getTableName(schema, appCode));

        sl.append(" set ");

        //增加修改人和修改时间
        fieldAndVal.put("modifiedBy", RequestContext.getCorpLoginId());
        fieldAndVal.put("modifiedTime", new Date());

        this.fieldAndVal.forEach((k, v) -> sl.append(k).append("=:").append(k).append(","));

        sl.deleteCharAt(sl.length() - 1);

        param.putAll(fieldAndVal);

        sl.append(where());

        Console.log("================update sql={}",sl.toString());
        Console.log("================update param={}",param);

        return jdbcTemplate.update(sl.toString(), param);
    }


    public int insert(){

        Assert.notNull(schema,"表命不能为空");
        Assert.notEmpty(fieldAndVal,"create 字段和值不能为空");

        StringBuilder sl = new StringBuilder("insert into ").append(getTableName(schema,appCode));

        sl.append(" (");
        StringBuilder valSql = new StringBuilder(" values (");
        boolean isNotFirst = false;

        for (Map.Entry<String, Object> entry : fieldAndVal.entrySet()  ) {
            if (isNotFirst) {
                sl.append(",").append(entry.getKey());
                valSql.append(",").append(":").append(entry.getKey());
            }else{
                isNotFirst = true;
                sl.append(entry.getKey());
                valSql.append(":").append(entry.getKey());
            }
        }

        sl.append(") ");
        valSql.append(")");
        sl.append(valSql);

        Console.log("================create sql={}",sl.toString());
        Console.log("================create fieldAndVal={}",fieldAndVal);

        return jdbcTemplate.update(sl.toString(), fieldAndVal);
    }

    public int[] insertBatch(){

        Assert.notNull(schema,"表命不能为空");
        Assert.notEmpty(fieldAndValList,"create 字段和值不能为空");

        StringBuilder sl = new StringBuilder("insert into ").append(getTableName(schema,appCode));

        sl.append(" (");
        StringBuilder valSql = new StringBuilder(" values (");
        boolean isNotFirst = false;

        for (Map.Entry<String, Object> entry : fieldAndValList[0].entrySet()  ) {
            if (isNotFirst) {
                sl.append(",").append(entry.getKey());
                valSql.append(",").append(":").append(entry.getKey());
            }else{
                isNotFirst = true;
                sl.append(entry.getKey());
                valSql.append(":").append(entry.getKey());
            }
        }

        sl.append(") ");
        valSql.append(")");
        sl.append(valSql);

        Console.log("================create sql={}",sl.toString());
        Console.log("================create fieldAndVal={}",fieldAndValList);


        return jdbcTemplate.batchUpdate(sl.toString(),fieldAndValList);
    }

    public BOList jdbcResultToBOList(int pageSize, int pageIndex, List<Map<String,Object>> resultData) {
        ListBOQuery query = new ListBOQuery();
        query.setSchemaCode(this.schema);
        query.setPageSize(pageSize);
        query.setPageIndex(pageIndex);
        List<Object> idList = resultData.stream().map(map -> map.get("id")).collect(Collectors.toList());
        Condition condition = new SimpleCondition("id", Operator.IN, idList);
        query.setCondition(condition);
        return this.boService.list(query).getData();
    }


    private StringBuilder where() {
        StringBuilder where = new StringBuilder();
        if (condition == null) {
            return where;
        }


        ComplexCondition c;
        int step = 0;
        boolean isNotFist = false;

        Iterator<ComplexCondition> iterator = condition.iterator();


        while (iterator.hasNext()) {
            ComplexCondition next = iterator.next();
            List<Condition> conditions = next.getConditions();

            StringBuilder natrue = new StringBuilder(" ").append(next.getNature().name()).append(" ");
            for (Condition s : conditions) {
                if (s instanceof SimpleCondition) {

                    SimpleCondition sc = (SimpleCondition) s;
                    if (isNotFist) {
                        where.append(natrue);
                    } else {
                        isNotFist = true;
                    }
                    ++ step ;
                    doWere(sc, where, step);
                }

            }
        }

        if (where.length() != 0) {
            where = new StringBuilder(" where ").append(where);
        }

        return  where;
    }

    private void doWere(SimpleCondition sc,StringBuilder where,int step){
        StringBuilder key = new StringBuilder("param").append(step);
        where.append(sc.getField());

        switch (sc.getOperator()) {
            case EQ:
                where.append(" = :").append(key);
                break;
            case NOT_EQ:
                where.append(" != :").append(key);
                break;
            case IN:
                where.append(" in (:").append(key).append(")");
                break;
            case NOT_IN:
                where.append(" not in (:").append(key).append(")");
                break;
            case LIKE:
                where.append(" like :").append(key);
                break;
            case NOT_LIKE:
                where.append(" not like :").append(key);
                break;
            case GT:
                where.append(" > :").append(key);
                break;
            case GTE:
                where.append(" >= :").append(key);
                break;
            case LT:
                where.append(" < :").append(key);
                break;
            case LTE:
                where.append(" <= :").append(key);
                break;
            default:
                Console.log("其他的自己拓展");
                break;
        }

        Object val = sc.getValue();

        if (sc.getOperator()==Operator.LIKE || Operator.NOT_LIKE ==sc.getOperator()){
            val = "%".concat(String.valueOf(val)).concat("%");
        }

        param.put(key.toString(), val);
    }




    public static void main(String[] args) {


        // update
        int update = JdbcTemplateUtils.builder("test").putFieldAndVal("a", new Date())
                .putFieldAndVal("b", 1)
                .eq("a", new Date()).eq("b", 3).update();

        Console.log("===============update ={}", update);


        //select
        List<Integer> integers = JdbcTemplateUtils.builder("test").selectField("a,b")
                .selectField("c as testC").eq("a", 1)
                .eq("b", 2).or().eq("b", 3).queryForList(Integer.class);

    }



}
